-- 샘플 데이터 insert 부분입니다. by Harry

INSERT INTO CHATTING_MESSAGE(chatt_no, message, from_nick, to_nick, from_id, to_id, send_time
	 ) VALUES(1, 'test', 'nick_01', 'nick_02', 'id_01', 'id_02', to_char(sysdate,'yyyy-mm-dd-hh24:mi:dd'))
);

DROP TABLE MEMBER cascade constraints;

insert into member(
		id, password, nickname, name, gender
		, birth, hp, email, city, town
		, self_introduction, login_check, role
	) values(
		'admin', '0000', '탑게이', '관리자', 'm'
		, '19800101', '010-1234-5678', 'koreaken01@gmail.com', '경기도', '성남시'
		, '관리자 입니다.', 1, 'admin'
);
insert into member(
		id, password, nickname, name, gender
		, birth, hp, email, city, town
		, self_introduction, login_check
	) values(
		'test1', '0000', '박근', '안철수', 'm'
		, '19800101', '010-1234-5678', 'koreaken01@gmail.com', '서울시', '마포구'
		, 'test1입니다.', 1
);
insert into member(
		id, password, nickname, name, gender
		, birth, hp, email, city, town
		, self_introduction, login_check
	) values(
		'test2', '0000', '김근', '김철수', 'm'
		, '19800101', '010-1234-5678', 'koreaken01@gmail.com', '서울시', '마포구'
		, 'test2입니다.', 1
);
insert into member(
		id, password, nickname, name, gender
		, birth, hp, email, city, town
		, self_introduction, login_check
	) values(
		'test3', '0000', '이근', '이철수', 'm'
		, '19800101', '010-1234-5678', 'koreaken01@gmail.com', '서울시', '마포구'
		, 'test3입니다.', 1
);
select * from member;


insert into cscenter (
  		cscenter_no, id, nickname, email, divide, divide_no
		, title, content, view_cnt
	) VALUES(
		1, 'admin', '탑게이', 'amin@topgay.com', '5', 1
		, 'test01', 'test01내용', 0
);
select * from cscenter;
select nvl(max(divide_no), 0) from cscenter where divide=5;

DROP TABLE cscenter cascade constraints;
CREATE TABLE cscenter (
	cscenter_no   NUMBER       NOT NULL, -- 글번호
	id         VARCHAR2(20)  NOT NULL, -- 아이디
	nickname   VARCHAR2(20)  NOT NULL, -- 닉네임
	email		VARCHAR2(50)	NOT NULL, --이메일
	divide     	INTEGER       NOT NULL, -- 구분 - 5:notice/6:faq/7:qna
	divide_no 	NUMBER       DEFAULT 0 NOT NULL, -- 구분별 글번호
	title      	VARCHAR2(255)  NOT NULL, -- 제목
	content    	CLOB NOT NULL, -- 내용
	result     	VARCHAR2(4)	NULL,     -- 처리결과 (NULL)/DO/DONE
	view_cnt    NUMBER       NOT NULL, -- 조회수
	cre_date   	VARCHAR2(14)	 DEFAULT to_char(sysdate, 'yyyymmddhh24miss') NOT NULL, -- 작성일
	del_date   	VARCHAR2(14)     NULL      -- 삭제일
	, constraint cscenter_no_pk primary key(cscenter_no)
	, constraint cscenter_member_id_fk foreign key(id) references member(id) on delete cascade
);
insert into cscenter (cscenter_no, id, nickname, email, divide, divide_no, title, content, view_cnt
) values (
1, 'admin', '관리자', 'amin@topgay.com', 5, 
1, '공지사항 제목', '공지사항 내용', 0);

select * from cscenter;
insert into cscenter (
  		cscenter_no, id, nickname, email, divide
  		, divide_no, title, content, view_cnt
	) values (
		(select cscenter_no_seq.nextVal from dual), 'admin', '탑게이', 'amin@topgay.com', 6
		, (select nvl(max(divide_no), 0)+1 from cscenter where divide=6)
		, 'test', 'content', (select nvl(max(view_cnt), 0)+1 from cscenter where cscenter_no=(select cscenter_no_seq.nextVal from dual))
	);
	
insert into cscenter (
  		cscenter_no, id, nickname, email, divide
  		, divide_no, title, content, view_cnt
) values (
		(select cscenter_no_seq.nextVal from dual), 'admin', '탑게이', 'amin@topgay.com', 6
		, 1, 'test', 'content', 1
);
select (select nvl(max(divide_no), 0)+1 from cscenter where divide=#{divide}) a from cscenter;

select seq_seq.nextval seq1, 'PID'||seq_seq2.nextval seq2 from dual

drop sequence cscenter_divide_no1_seq;
create sequence cscenter_divide_no1_seq
nocycle
nocache;

drop sequence cscenter_no_seq;
create sequence cscenter_no_seq
nocycle
nocache;

drop table cscenter;

select cscenter_no_seq.nextval no1, cscenter_divide_no1_seq.nextval no2 from dual;
select * from cscenter;

update cscenter set
title='title'
, view_cnt=(select nvl(max(view_cnt), 0) +1 from cscenter where divide=5)
where cscenter_no=12;

select cscenter_no_seq.nextval no1 from dual;


update cscenter    set      , nickname=?     , email=?          , title=?     , content=?          , cre_date=to_char(sysdate, 'yyyymmddhh24miss')  where cscenter_no=?


